# db_utils.py

import sqlite3
from typing import List, Tuple

def get_db_connection():
    """获取SQLite数据库连接
    
    Returns:
        connection: SQLite数据库连接对象
    """
    connection = sqlite3.connect('inventory.db')
    return connection

def add_drug(name: str, quantity: int, supplier: str, purchase_date: str):
    """将药品信息添加到药品信息表和入库记录表中
    
    Args:
        name: 药品名称
        quantity: 药品数量
        supplier: 供应商
        purchase_date: 进货时间
    """
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        # 查看药品是否已经在药品信息表中
        cursor.execute('SELECT drug_id FROM drug_info WHERE name = ?', (name,))
        result = cursor.fetchone()

        if result:
            # 药品已存在，更新总库存量
            drug_id = result[0]
            cursor.execute('UPDATE drug_info SET total_quantity = total_quantity + ? WHERE drug_id = ?',
                           (quantity, drug_id))
        else:
            # 药品不存在，插入新记录
            cursor.execute('INSERT INTO drug_info (name, total_quantity) VALUES (?, ?)',
                           (name, quantity))
            drug_id = cursor.lastrowid

        # 记录入库信息
        cursor.execute('INSERT INTO stock_in (drug_id, quantity, purchase_date, supplier) VALUES (?, ?, ?, ?)',
                       (drug_id, quantity, purchase_date, supplier))

        connection.commit()
    finally:
        cursor.close()
        connection.close()

def update_drug_quantity(drug_id: int, quantity_change: int):
    """更新药品表中的药品总库存量
    
    Args:
        drug_id: 药品ID
        quantity_change: 数量变化
    """
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute('UPDATE drug_info SET total_quantity = total_quantity + ? WHERE drug_id = ?',
                       (quantity_change, drug_id))
        
        connection.commit()
    finally:
        cursor.close()
        connection.close()

def remove_drug(drug_id: int, quantity: int, department: str, person: str, usage_date: str):
    """将药品出库记录添加到出库记录表，并更新药品总库存量
    
    Args:
        drug_id: 药品ID
        quantity: 数量
        department: 部门
        person: 领用人
        usage_date: 出库时间
    """
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        # 更新药品总库存量
        cursor.execute('UPDATE drug_info SET total_quantity = total_quantity - ? WHERE drug_id = ?',
                       (quantity, drug_id))

        # 记录出库信息
        cursor.execute('INSERT INTO stock_out (drug_id, quantity, usage_date, department, person) VALUES (?, ?, ?, ?, ?)',
                       (drug_id, quantity, usage_date, department, person))

        connection.commit()
    finally:
        cursor.close()
        connection.close()

def check_low_stock() -> List[Tuple[str, int]]:
    """检查药品库存是否低于初始数量的5%，并返回库存低的药品列表
    
    Returns:
        low_stock_items: 库存低于警戒线的药品列表
    """
    connection = get_db_connection()
    cursor = connection.cursor()
    low_stock_items = []

    try:
        # 查询药品总库存量和初始数量的5%的药品
        cursor.execute('SELECT name, total_quantity FROM drug_info WHERE total_quantity <= 5')
        low_stock_items = cursor.fetchall()
    finally:
        cursor.close()
        connection.close()

    return low_stock_items
